package com.sp2p.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.web.context.ContextLoader;

import com.sp2p.dao.InvestDao;
import com.sp2p.dao.RepayMentDao;
import com.sp2p.dao.admin.BorrowManageDao;
import com.sp2p.constants.IAmountConstants;
import com.sp2p.util.AmountUtil;
import com.sp2p.util.DateUtil;
import com.sp2p.dao.FrontMyPaymentDao;
import com.shove.Convert;
import com.shove.base.BaseService;
import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.security.Encrypt;
import com.shove.util.BeanMapUtils;
import com.shove.util.UtilDate;
import com.shove.vo.PageBean;
import com.sp2p.constants.IConstants;
import com.sp2p.constants.IFundConstants;
import com.sp2p.constants.IInformTemplateConstants;
import com.sp2p.dao.AccountUsersDao;
import com.sp2p.dao.BorrowDao;
import com.sp2p.dao.FinanceDao;
import com.sp2p.dao.OperationLogDao;
import com.sp2p.dao.UserDao;
import com.sp2p.database.Dao;
import com.sp2p.database.Dao.Procedures;
import com.sp2p.database.Dao.Views.intentionfund_user;

/**
 * @ClassName: FinanceService.java
 * @Author: gang.lv
 * @Date: 2013-3-4 上午11:14:21
 * @Copyright: 2013 www.emis.com Inc. All rights reserved.
 * @Version: V1.0.1
 * @Descrb: 理财业务处理层
 */
public class FinanceService extends BaseService {

	public static Log log = LogFactory.getLog(FinanceService.class);

	private FinanceDao financeDao;
	private AwardService awardService;
	private SelectedService selectedService;
	private UserDao userDao;
	private OperationLogDao operationLogDao;
	private RepayMentDao repayMentDao;
	private InvestDao investDao;
	private BorrowDao borrowDao;
	
	/**
	 * @Return:
	 * @Descb: 购买债权
	 * @Throws:
	 */
	public Map<String, String> addDebtInvest(long debtId, long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		Map<String, String> userMap = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_tzsh_buy(conn, ds, outParameterValues, debtId, userId,0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_invest t_assignment_debt", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "购买债权", 1);
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 添加新手专享借款投资记录
	 * @Throws:
	 */
	public Map<String, String> addXsBorrowInvest(long id, long userId,
			double investAmount, String basePath,String username) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		Map<String, String> userMap = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_xs_borrow_join(conn, ds, outParameterValues, id, userId,
					basePath, new BigDecimal(investAmount), new Date(),4,0,1,0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_invest", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "新手专享投资", 1);
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 添加稳盈宝借款投资记录
	 * @Throws:
	 */
	public Map<String, String> addBorrowInvest(long id, long userId,
			double investAmount, String basePath,String username) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		Map<String, String> userMap = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_borrow_join(conn, ds, outParameterValues, id, userId,
					basePath, new BigDecimal(investAmount), new Date(),4,0,1,0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_invest", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "稳赢宝投资", 1);
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/**
	 * @throws Exception
	 * @MethodName: addBorrowInvest
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午05:48:20
	 * @Return:
	 * @Descb: 添加余额宝借款投资记录
	 * @Throws:
	 */
	public Map<String, String> addYebProductBorrowInvest(long pid, long userId,
			double investAmount, String basePath,String username) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		Map<String, String> userMap = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_product_yeb_join(conn, ds, outParameterValues, pid, userId,
					basePath, new BigDecimal(investAmount), new Date(),0, "");
			
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			
			if (ret <= 0) {
				conn.rollback();
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_invest", 
						Convert.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "余额宝投资", 1);
			}
			
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/**
	 * @throws Exception
	 * @MethodName: addBorrowInvest
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午05:48:20
	 * @Return:
	 * @Descb: 添加月利宝、季发宝借款投资记录
	 * @Throws:
	 */
	public Map<String, String> addProductBorrowInvest(long pid, long userId,
			double investAmount, String basePath,String username,int pType,int profitDisposal) throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		Map<String, String> userMap = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_product_join(conn, ds, outParameterValues, pid, userId,
					basePath, new BigDecimal(investAmount), new Date(),profitDisposal,0, "");
			
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			
			if (ret <= 0) {
				conn.rollback();
			} else {
				userMap = userDao.queryUserById(conn, userId);
				String content = "";
				if(pType == 2){
					content = "月利宝投资";
				}else if(pType == 3){
					content = "季发宝投资";
				}
				operationLogDao.addOperationLog(conn, "t_invest", 
						Convert.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, content, 1);
			}
			
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/**
	 * @throws Exception
	 * @Return:
	 * @Descb: 购买余额宝债权
	 * @Throws:
	 */
	public double buyYebDebt(long pid, long userId,double investAmount) 
		throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		List<Object> outParameterValues = new ArrayList<Object>();
		double remainAmount = 0;
		try {
			double amount = 0;
			Map<String,String> yebDebtMap = financeDao.queryYebDebt(conn, pid, userId, investAmount);
			if(yebDebtMap.size() > 0){
				long debtId = Convert.strToLong(yebDebtMap.get("id"), -1);
				double debtSum = Convert.strToLong(yebDebtMap.get("debtSum"), -1);
				//投资金额大于债权金额，拆分投资金额
				amount = ( investAmount == debtSum) ? investAmount : debtSum;
				if(debtId > 0){
					Procedures.p_yeb_tzsh_buy(conn, ds, outParameterValues, debtId, userId, amount, 0, "");
					ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
				}
			}
			remainAmount = investAmount - amount;
			
			if (ret <= 0) {
				conn.rollback();
				remainAmount = investAmount;
			} 
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return remainAmount;
	}
	
	
	
	
	
	
	

	/**
	 * @MethodName: queryBorrowByCondition
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-4 下午05:01:31
	 * @Return:
	 * @Descb: 根据条件查询借款信息
	 * @Throws:
	 */
	public void queryBorrowByCondition(String borrowStatus, String borrowWay,
			String title, String paymentMode, String purpose, String deadline,
			String reward, String arStart, String arEnd, String order,
			PageBean pageBean) throws Exception {
		String resultFeilds = " id,borrowShow,purpose,imgPath,borrowWay,investNum,borrowTitle,username,vipStatus,credit,creditrating,borrowAmount,annualRate,deadline,excitationType,excitationSum,borrowStatus,schedules,vip,hasPWD,isDayThe,auditStatus ";
		StringBuffer condition = new StringBuffer();
		condition.append(" and sorts!= 0 ");
		Connection conn = MySQL.getConnection();
		if (StringUtils.isNotBlank(borrowStatus)) {
			condition.append(" and borrowStatus in"
					+ StringEscapeUtils.escapeSql(borrowStatus));
		}
		if (StringUtils.isNotBlank(borrowWay)) {
			condition.append(" and borrowWay in"
					+ StringEscapeUtils.escapeSql(borrowWay));
		}
		if (StringUtils.isNotBlank(title)) {
			condition.append(" and borrowTitle  LIKE CONCAT('%','"
					+ StringEscapeUtils.escapeSql(title.trim()) + "','%')");
		}
		if (StringUtils.isNotBlank(paymentMode)
				&& StringUtils.isNumericSpace(paymentMode)) {
			condition.append(" and paymentMode ="
					+ StringEscapeUtils.escapeSql(paymentMode));
		}
		if (StringUtils.isNotBlank(purpose)
				&& StringUtils.isNumericSpace(purpose)) {
			condition.append(" and purpose ="
					+ StringEscapeUtils.escapeSql(purpose));
		}
		if (StringUtils.isNotBlank(deadline)
				&& StringUtils.isNumericSpace(deadline)) {
			condition.append(" and deadline ="
					+ StringEscapeUtils.escapeSql(deadline));
		}
		if (StringUtils.isNotBlank(reward)
				&& StringUtils.isNumericSpace(reward)) {
			if ("1".equals(reward)) {
				condition.append(" and excitationType ="
						+ StringEscapeUtils.escapeSql(reward));
			} else {
				condition.append(" and excitationType > 1 ");
			}
		}
		if (StringUtils.isNotBlank(arStart)
				&& StringUtils.isNumericSpace(arStart)) {
			condition.append(" and amount > "
					+ StringEscapeUtils.escapeSql(arStart));
		}
		if (StringUtils.isNotBlank(arEnd) && StringUtils.isNumericSpace(arEnd)) {
			condition.append(" and amount <"
					+ StringEscapeUtils.escapeSql(arEnd));
		}
		try {
			dataPage(conn, pageBean, " v_t_borrow_list", resultFeilds,
					" order by sorts desc,schedules asc ,id desc ", condition
							.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:18:19
	 * @Return:
	 * @Descb: 根据ID查询债权详细信息
	 * @Throws:
	 */
	public Map<String, String> queryDebtDetailById(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryDebtDetailById(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 根据ID、product、isForNewUser查询借款详细信息
	 * @Throws:
	 */
	public Map<String, String> queryBorrowDetail(long id,int product,int isForNewUser) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryBorrowDetail(conn,id,product,isForNewUser);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	
	/**
	 * @Return:稳赢宝
	 * @Descb: 查询借款详细信息(根据投资期限，收益利率，可投资筛选)
	 * @Throws:queryDebtList
	 */
	public List<Map<String, Object>> queryBorrowList() throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryBorrowList(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	
	/**
	 * @Return:债权
	 * @Descb: 查询债权详细信息(根据投资期限，收益利率，可投资筛选)
	 * @Throws:
	 */
	public List<Map<String, Object>> queryDebtList(Long alienatorId) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryDebtList(conn,alienatorId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	

	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:18:19
	 * @Return:
	 * @Descb: 根据ID查询借款详细信息
	 * @Throws:
	 */
	public Map<String, String> queryBorrowDetailById(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryBorrowDetailById(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:18:19
	 * @Return:
	 * @Descb: 根据ID查询借款产品详细信息
	 * @Throws:
	 */
	public Map<String, String> queryProductDetailById(long id,int borrowProduct) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryProductDetailById(conn, id,borrowProduct);
			map.putAll(financeDao.queryYebTotalYd(conn, borrowProduct));
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/**
	 * @MethodName: queryBorrowDetailById
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:18:19
	 * @Return:
	 * @Descb: 查询余额宝利率
	 * @Throws:
	 */
	public Map<String, String> queryYebAnnualRate() throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryYebAnnualRate(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/***
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> queryEveryAmountById(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryEveryAmountById(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @MethodName: queryUserInfoById
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午06:04:54
	 * @Return:
	 * @Descb: 根据ID查询借款信息发布者个人信息
	 * @Throws:
	 */
	public Map<String, String> queryUserInfoById(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryUserInfoById(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @MethodName: queryUserIdentifiedByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:00:04
	 * @Return:
	 * @Descb: 根据ID查询用户认证信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryUserIdentifiedByid(long id)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryUserIdentifiedByid(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return list;
	}

	/**
	 * @MethodName: queryUserImageByid
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-4-16 上午11:01:28
	 * @Return:
	 * @Descb: 查询用户认证图片
	 * @Throws:
	 */
	public List<Map<String, Object>> queryUserImageByid(long typeId, long userId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryUserImageByid(conn, typeId, userId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return list;
	}

	/**
	 * @MethodName: queryPaymentByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:03:01
	 * @Return:
	 * @Descb: 根据ID查询本期还款信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryRePayByid(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryRePayByid(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return list;
	}

	/**
	 * @MethodName: queryCollectionByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:04:28
	 * @Return:
	 * @Descb: 根据ID查询本期催收信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryCollectionByid(long id)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryCollectionByid(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return list;
	}

	/**
	 * @MethodName: queryInvestByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:06:00
	 * @Return:
	 * @Descb: 根据ID查询投资记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryInvestByid(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryInvestByid(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * @MethodName: queryInvestByid
	 * @Param: FinanceDao
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:06:00
	 * @Return:
	 * @Descb: 根据Userid和InvestID查询余额宝详情
	 * @Throws:
	 */
	
	public Map<String, String> queryYebInvestByInvestId(long userid,long investid) throws Exception {
		StringBuffer command = new StringBuffer();
		Connection conn = connectionManager.getConnection();
		try {
			command.append(" select userid,username,realname,idno,SN,InvestAmount,annualRate,dailyInterest,totalSum,date_format(investTime,' %Y 年 %m 月 %d 日') as investTime  ");
			command.append(" from v_t_yeb_invest_detail ");
			command.append(" where userid = " + userid);
			command.append(" and investId = " + investid);
			DataSet dataSet = MySQL.executeQuery(conn, command.toString());
			return BeanMapUtils.dataSetToMap(dataSet);
		} finally {
			conn.close();
		}
		}
	
	/**
	 * @MethodName: queryInvestByid
	 * @Param: 
	 * @Author: 
	 * @Date: 
	 * @Return:
	 * @Descb: 根据borrowId分页查询稳盈宝投资记录
	 * @Throws:
	 */
	public void queryInvestBybId(long id,PageBean pageBean) throws Exception,
	DataException {
		Connection conn = MySQL.getConnection();
		String field = "";
		field += " id ,borrowId ,borrowProduct, f_formatting_username(username) as username , investAmount ,invAmount, ";
		field += " investTime , investor , isDebt ,oriInvestor, f_formatting_username(oriInvestorUsername) as oriInvestorUsername, ";
		field += " deadline,recievedInterest,recivedPrincipal,username as fullname,isAutoBid ";
		
		try {
			dataPage(conn, pageBean, " v_t_borrow_investrecord ", field, 
			" order by investTime asc ", " and ( borrowId = " + id + " or parentBId = " + id + ")");
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryYebInvestByid
	 * @Param: 
	 * @Author: 
	 * @Date: 
	 * @Return:
	 * @Descb: 根据productId分页查询余额宝投资记录
	 * @Throws:
	 */
	public void queryYebInvestBypId(long id,PageBean pageBean) throws Exception,
	DataException {
		Connection conn = MySQL.getConnection();
		String field = "";
		field += " id,investId,userId,f_formatting_username(username) as username,productId,borrowId,SN,investAmount,freezeAmount,";
		field += " hasRedeemAmount,interestSum,dailyInterest,status,annualRate,principalInterestSum,redeemFee,investTime,interestDay ";
		
		try {
			dataPage(conn, pageBean, " v_t_yeb_invest_detail ", field, 
			" order by investTime asc ", " and productId = " + id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryInvestByid
	 * @Param: 
	 * @Author: 
	 * @Date: 
	 * @Return:
	 * @Descb: 根据productId分页查询月利宝和季发宝投资记录
	 * @Throws:
	 */
	public void queryInvestBypId(long id,PageBean pageBean) throws Exception,
	DataException {
		Connection conn = MySQL.getConnection();
		String field = "";
		field += " id ,borrowId , f_formatting_username(username) as username , investAmount ,invAmount, ";
		field += " investTime , investor,isDebt ,oriInvestor, f_formatting_username(oriInvestorUsername) as oriInvestorUsername, ";
		field += " deadline,recievedInterest,recivedPrincipal,username as fullname,isAutoBid ";
		
		try {
			dataPage(conn, pageBean, " v_t_borrow_investrecord ", field, 
			" order by investTime asc ", " and productId = " + id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: queryBorrowMSGBord
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-6 下午08:30:26
	 * @Return:
	 * @Descb: 根据ID查询留言板信息
	 * @Throws:
	 */
	public void queryBorrowMSGBord(long id, PageBean pageBean) throws Exception {
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_msgbord", " * ",
					" order by id desc ", " and modeId=" + id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: addBrowseCount
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-5 下午03:54:02
	 * @Return:
	 * @Descb: 添加浏览量处理
	 * @Throws:
	 */
	public void addBrowseCount(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		long returnId = -1L;
		try {
			returnId = financeDao.addBrowseCount(conn, id);
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @throws DataException
	 * @MethodName: addBorrowMSG
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-13 上午08:16:45
	 * @Return:
	 * @Descb: 添加借款留言
	 * @Throws:
	 */
	public long addBorrowMSG(long id, long userId, String msgContent)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		long returnId = -1;
		try {
			returnId = financeDao.addBorrowMSG(conn, id, userId, msgContent);
			if (returnId <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_msgboard", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "添加借款留言", 1);
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return returnId;
	}

	/**
	 * @MethodName: getInvestStatus
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-11 下午06:46:17
	 * @Return:
	 * @Descb: 获取借款的投标状态,条件是正在招标中
	 * @Throws:
	 */
	public Map<String, String> getInvestStatus(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.getInvestStatus(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @MethodName: valideInvest
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午04:07:59
	 * @Return:
	 * @Descb: 验证投资人是否符合本次投标
	 * @Throws:
	 */
	public String valideInvest(long id, long userId, double amount)
			throws Exception {
		Connection conn = MySQL.getConnection();
		String result = "";
		try {
			result = financeDao.valideInvest(conn, id, userId, amount);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}

	/**
	 * @MethodName: valideTradePassWord
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午04:07:43
	 * @Return:
	 * @Descb: 验证交易密码
	 * @Throws:
	 */
	public String valideTradePassWord(long userId, String pwd) throws Exception {
		Connection conn = MySQL.getConnection();
		String result = "";
		try {
			result = financeDao.valideTradePassWord(conn, userId, pwd);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}

	/**
	 * @MethodName: queryBorrowInvest
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-11 下午07:30:26
	 * @Return:
	 * @Descb: 根据ID获取投资的借款信息
	 * @Throws:
	 */
	public Map<String, String> queryBorrowInvest(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryBorrowInvest(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:48:43
	 * @Return:
	 * @Descb: 查询用户的金额
	 * @Throws:
	 */
	public Map<String, String> queryUserMonney(long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryUserMonney(conn, userId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:48:43
	 * @Return:
	 * @Descb: 查询投标是否满额
	 * @Throws:
	 */
	public Map<String, String> queryIsFull(long borrowId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryIsFull(conn, borrowId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	
	/**
	 * @MethodName: queryUserMonney
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-12 下午08:48:43
	 * @Return:
	 * @Descb: 更新满标时间
	 * @Throws:
	 */
	public long updateFullBorrowTime(long borrowId) throws Exception {
		Connection conn = MySQL.getConnection();
		long result=0;
		try {
			result = financeDao.updateFullBorrowTime(conn, borrowId);
			if(result>0){
				conn.commit();
			}
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();
			throw e;
		} finally {
			conn.close();
		}

		return result;
	}


	/**
	 * @throws DataException
	 * @MethodName: addFocusOn
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-16 上午09:00:49
	 * @Return:
	 * @Descb: 添加关注
	 * @Throws:
	 */
	public Long addFocusOn(long id, long userId, int modeType) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			result = financeDao.addFocusOn(conn, id, userId, modeType);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, userId);
				if (modeType == 1) {
					operationLogDao.addOperationLog(conn, "t_concern", Convert
							.strToStr(userMap.get("username"), ""),
							IConstants.INSERT, Convert.strToStr(userMap
									.get("lastIP"), ""), 0, "添加关注用户", 1);
				} else {
					operationLogDao.addOperationLog(conn, "t_concern", Convert
							.strToStr(userMap.get("username"), ""),
							IConstants.INSERT, Convert.strToStr(userMap
									.get("lastIP"), ""), 0, "添加关注借款", 1);
				}
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}

	/**
	 * @throws DataException
	 * @MethodName: hasFocusOn
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-16 上午11:04:13
	 * @Return:
	 * @Descb: 查询用户是否已经有关注
	 * @Throws:
	 */
	public Map<String, String> hasFocusOn(long id, long userId, int moduleType)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.hasFocusOn(conn, id, userId, moduleType);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @throws DataException
	 * @MethodName: addUserMail
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-16 下午10:13:57
	 * @Return:
	 * @Descb: 添加用户站内信
	 * @Throws:
	 */
	public long addUserMail(long reciver, Long userId, String title,
			String content, int mailType) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			result = financeDao.addUserMail(conn, reciver, userId, title,
					content, mailType);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_concern", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "发送站内信", 1);
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return result;
	}

	/**
	 * @throws DataException
	 * @MethodName: addUserReport
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-16 下午10:15:05
	 * @Return:
	 * @Descb: 添加用户举报
	 * @Throws:
	 */
	public long addUserReport(long reporter, Long userId, String title,
			String content) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> userMap = new HashMap<String, String>();
		Long result = -1L;
		try {
			result = financeDao.addUserReport(conn, reporter, userId, title,
					content);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			} else {
				userMap = userDao.queryUserById(conn, userId);
				operationLogDao.addOperationLog(conn, "t_report", Convert
						.strToStr(userMap.get("username"), ""),
						IConstants.INSERT, Convert.strToStr(userMap
								.get("lastIP"), ""), 0, "添加用户举报", 1);
			}

			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}
	
	public List<Map<String, Object>> queryJrlLastestBorrow() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryJrlLastestBorrow(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	
	/**
	 * @MethodName: queryJxbLastestBorrow
	 * @Param: FinanceService
	 * @Author: Mr.Zero
	 * @Date: 2017-4-18 下午午16:00:00
	 * @Return:
	 * @Descb: 查询加薪宝
	 * @Throws:
	 */
	public List<Map<String, Object>> queryJxbLastestBorrow() throws Exception,
		DataException {
	Connection conn = MySQL.getConnection();
	List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
	try {
		list = financeDao.queryJxbLastestBorrow(conn);
	} catch (Exception e) {
		log.error(e);
		e.printStackTrace();
	
		throw e;
	} finally {
		conn.close();
	}
	return list;
	}

	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午09:28:00
	 * @Return:
	 * @Descb: 查询最新的借款前5条记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryLastestBorrow() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryLastestBorrow(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	
	public Map<String, String> queryWyb() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryWyb(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @Return:
	 * @Descb: 查询最新的新手借款前5条记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryLastestNewUserBorrow() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryLastestNewUserBorrow(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	//查询最新的新手借款记录
	public Map<String, String> queryNewUserBorrow() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryNewUserBorrow(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午09:28:00
	 * @Return:
	 * @Descb: 查询最新的债权前5条记录
	 * @Throws:
	 */
	public List<Map<String, Object>> queryLastestDebt() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryLastestDebt(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	//查询最新的债权记录
	public Map<String, String> queryDebt() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryDebt(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午09:28:00
	 * @Return:
	 * @Descb: 查询借款产品列表
	 * @Throws:
	 */
	public Map<String, String> queryBorrowProduct(int productType) throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.querytBorrowProduct(conn , productType);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午09:28:00
	 * @Return:
	 * @Descb:查询所有招标中的借款
	 * @Throws:
	 */
	public List<Map<String, Object>> queryAllBorrowTenderIn() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryAllBorrowTenderIn(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	
	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: tanghaifeng
	 * @Date: 2013-10-29 上午09:28:00
	 * @Return:
	 * @Descb: 分页查询我要借款列表
	 * @Throws:
	 */
	public void queryLastestBorrowPage(PageBean pageBean) throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_index ", " *,date_format(`fullBorrowTime`, '%m.%d %H:%i') AS `fullBorrowTimeFormat` ", 
					" order by borrowStatus asc , schedules desc  ", "and sorts != 0 and borrowStatus > 1 " +
							" and borrowProduct = 4 and parentBId < 0  and isForNewUser = 0 and agent in (75,76) ");
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @Descb: 分页查询新手借款列表
	 * @Throws:
	 */
	public void queryLastestXsBorrowPage(PageBean pageBean) throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_index ", " *,date_format(`fullBorrowTime`, '%m.%d %H:%i') AS `fullBorrowTimeFormat` ", 
					" order by borrowStatus asc , schedules desc  ", " and sorts != 0 and borrowStatus > 1 " +
							" and borrowProduct = 4 and parentBId < 0  and isForNewUser = 1 ");
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryLastestBorrow
	 * @Param: FinanceService
	 * @Author: tanghaifeng
	 * @Date: 2013-10-29 上午09:28:00
	 * @Return:
	 * @Descb: 分页查询我要借款列表
	 * @Throws:
	 */
	public void queryLastestDebtPage(PageBean pageBean) throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_assignment_debt ", "*", 
					" order by debtStatus asc,applyTime desc  ", " and debtStatus = 2 ");
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @MethodName: investRank
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午11:11:37
	 * @Return:
	 * @Descb: 投资排名前20条记录
	 * @Throws:
	 */
	public List<Map<String, Object>> investRank(int type, int count)
			throws Exception {
		Connection conn = MySQL.getConnection();
		// List<Map<String, Object>> list = new ArrayList<Map<String,
		// Object>>();
		DataSet ds = new DataSet();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			// list = financeDao.investRank(conn, starTime, endTime);
			Procedures.p_get_topinvestment(conn, ds, outParameterValues, type,
					count);

			conn.commit();
			ds.tables.get(0).rows.genRowsMap();
			return ds.tables.get(0).rows.rowsMap;
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

	}
	/**
	 * @MethodName: get_topNInvestment
	 * @Param: count
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午11:11:37
	 * @Return:
	 * @Descb: 投资排名前N条记录
	 * @Throws:
	 */
	public List<Map<String, Object>> get_topNInvestment(int count)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.get_topNInvestment(conn,count);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * @MethodName: get_mothTopNInvestment
	 * @Param: count
	 * @Author: gang.lv
	 * @Date: 2013-3-18 上午11:11:37
	 * @Return:
	 * @Descb: 本月新人王TOPN
	 * @Throws:
	 */
	public List<Map<String, Object>> get_mothTopNInvestment(int count)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.get_mothTopNInvestment(conn,count);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * 
	 * @Title: borrowerGeoDistribution
	
	 * @Description: TODO(借款地域分布)
	
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 */
	public List<Map<String, Object>> borrowerGeoDistribution()
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.borrowerGeoDistribution(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * 
	 * @Title: totalVolumeByMonth
	
	 * @Description: TODO(总借款成交金额)
	
	 * @return
	 * @throws Exception List<Map<String,Object>>
	 */
	public List<Map<String, String>> totalVolumeByMonth()
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		try {
			for(int i=7;i>0;i--){
				String timeStart = "";
				String timeEnd = "";
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
				SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM");
				Calendar lastDate = Calendar.getInstance();
				lastDate.add(Calendar.MONTH,-i);//减i个月
				lastDate.set(Calendar.DATE, 1);//把日期设置为当月第一天
				timeStart = sdf.format(lastDate.getTime());
				String month = sdf1.format(lastDate.getTime());
				lastDate.roll(Calendar.DATE, -1);//日期回滚一天，也就是本月最后一天
				timeEnd = sdf.format(lastDate.getTime());
				Map<String, String> totalVolumeByMonth = financeDao.totalVolumeByMonth(conn,timeStart,timeEnd);
				String dateTemp = (String)totalVolumeByMonth.get("updateDate");
				String totalVolumeTemp = (String)totalVolumeByMonth.get("totalVolume");
				if(StringUtils.isBlank(dateTemp) || StringUtils.isBlank(totalVolumeTemp)){
					totalVolumeByMonth.put("updateDate", month);
					totalVolumeByMonth.put("totalVolume", "0.00");
				}
				list.add(totalVolumeByMonth);
			}
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * 
	 * @Title: avgLoanFullTimeInSeconds
	
	 * @Description: TODO(每笔借款完成耗时)
	
	 * @return
	 * @throws Exception Map<String,String>
	 */
	public Map<String, String> avgLoanFullTimeInSeconds()
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.avgLoanFullTimeInSeconds(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * 
	 * @Title: avgCreditRightsSoldTimeInSeconds
	
	 * @Description: TODO(每笔债权转让时间 )
	
	 * @return
	 * @throws Exception Map<String,String>
	 */
	public Map<String, String> avgCreditRightsSoldTimeInSeconds()
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.avgCreditRightsSoldTimeInSeconds(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * @Title: investRankMore
	
	 * @Description: TODO 查看更多-投资排行榜
	
	 * @param pageBean
	 * @return List<Map<String,Object>>   
	 * @throws Exception 
	
	 */
	public List<Map<String, Object>> investRankMore(PageBean pageBean) throws Exception{
		Connection conn = MySQL.getConnection();
		DataSet ds = new DataSet();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			dataPage(conn, pageBean, "", "", "", "");
			conn.commit();
			ds.tables.get(0).rows.genRowsMap();
			return ds.tables.get(0).rows.rowsMap;
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();
			throw e;
		} finally {
			conn.close();
		}
	}
	/**
	 * @MethodName: queryTotalRisk
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 下午01:36:01
	 * @Return:
	 * @Descb: 查询风险保障金总额
	 * @Throws:
	 */
	public Map<String, String> queryTotalRisk() throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryTotalRisk(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @MethodName: queryCurrentRisk
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-18 下午01:36:14
	 * @Return:
	 * @Descb: 查询当日风险保障金收支金额
	 * @Throws:
	 */
	public Map<String, String> queryCurrentRisk() throws Exception,
			DataException {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryCurrentRisk(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * @throws DataException
	 * @throws Exception
	 * @MethodName: queryBorrowRecord
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-3-27 下午11:03:17
	 * @Return:
	 * @Descb: 查询借款记录统计
	 * @Throws:
	 */
	public Map<String, String> queryBorrowRecord(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getBorrowRecord(conn, ds, outParameterValues, id,
					new Date());
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}

		return map;
	}


	public FinanceDao getFinanceDao() {
		return financeDao;
	}

	public void setFinanceDao(FinanceDao financeDao) {
		this.financeDao = financeDao;
	}

	/**
	 * @throws Exception
	 * @MethodName: getInvestPWD
	 * @Param: FinanceService
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午05:35:00
	 * @Return:
	 * @Descb: 获取投标密码是否正确
	 * @Throws:
	 */
	public Map<String, String> getInvestPWD(Long idLong, String investPWD)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			if ("1".equals(IConstants.ENABLED_PASS)) {
				investPWD = com.shove.security.Encrypt.MD5(investPWD.trim());
			} else {
				investPWD = com.shove.security.Encrypt.MD5(investPWD.trim()
						+ IConstants.PASS_KEY);
			}
			map = financeDao.getInvestPWD(conn, idLong, investPWD);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * 根据借款Id查询还款记录
	 * 
	 * @param borrowId
	 * @return
	 * @throws Exception
	 * @throws DataException
	 */
	public List<Map<String, Object>> queryRepayment(long borrowId)
			throws Exception {

		List<Map<String, Object>> map = null;
		Connection conn = MySQL.getConnection();
		try {
			map = repayMentDao.queryHasPIAndStillPi(conn, borrowId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}


		return map;
	}

	/**
	 * 查找投资人信息 add by houli
	 * 
	 * @return
	 * @throws DataException
	 * @throws Exception
	 */
	public Map<String, String> queryInvestorById(long investorId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryInvestorById(conn, investorId, -1, -1);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * 获取离现在时间最近的一条定时发布的标的  add by tanghaifeng
	 * 
	 * @return
	 * @throws DataException
	 * @throws Exception
	 */
	public Map<String, String> queryBorrowByIstime() throws Exception{
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryBorrowByIstime(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * 根据发布借款信息的用户,查询该用户正在还款中的借款  add by tanghaifeng
	 * 
	 * @return
	 * @throws DataException
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryBorrowByUserId(String UserId) throws Exception{
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> map = new ArrayList<Map<String, Object>>();
		try {
			map = financeDao.queryBorrowByUserId(conn,UserId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * 倒计时结束后,更改该表的的状态值
	 * @param timeBorrowId
	 * @return
	 * @throws Exception
	 */
	public long changeTimeBorrowStaus(Long timeBorrowId) throws Exception {
		Connection conn = MySQL.getConnection();
		Long result = -1L;
		try {
			result = financeDao.changeTimeBorrowStaus(conn, timeBorrowId);
			if (result <= 0) {
				conn.rollback();
				return -1L;
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return result;
	}
	/**
	 * 获取该标的的担保公司
	 * @param agentId
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> getBorrowAgentByAgentId(long agentId) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.getBorrowAgentByAgentId(conn, agentId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	//根据借款编号获取披露信息
	public List<Map<String, Object>> getInfodisclosureList(Long bid)
		throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.getInfodisclosureList(conn,bid);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	//根据产品编号获取披露信息
	public List<Map<String, Object>> getInfodisclosureListByPid(Long pid)
		throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.getInfodisclosureListByPid(conn,pid);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	/**
	 * 根据投资者的ID,查找该投资真相关的投资信息
	 * @param investor
	 * @return
	 * @throws Exception
	 */
	public void queryInvestByInvestorId(long investor,PageBean<Map<String, Object>> pageBean) throws Exception{
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, "v_invest", "*", " ", " and oriInvestor=" + investor);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
	}
	/***
	 * 根据用户Id获取用户所在公司信息
	 * @param id
	 * @return
	 * @throws Exception 
	 */
	public Map<String, String> queryUserConpanyById(Long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String,String> map = new HashMap<String, String>();
		try {
			map = financeDao.queryUserConpanyById(conn,id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * 获取该标的的担保公司
	 * @param agentId
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> getInvestorReceipts(long investor) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = financeDao.getInvestorReceipts(conn, investor);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	/**
	 * 统计投资总额，理财总收益
	 * 
	 * @return
	 * @throws SQLException
	 */

	public Map<String, String> queryTotalInvest() throws Exception {
		Connection conn = MySQL.getConnection();

		Map<String, String> map = new HashMap<String, String>();
		try {
			map = investDao.queryTotalInvest(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * 统计随心投投资总额
	 * 
	 * @return
	 * @throws SQLException
	 */

	public Map<String, String> querySxtTotalInvest() throws Exception {
		Connection conn = MySQL.getConnection();

		Map<String, String> map = new HashMap<String, String>();
		try {
			map = investDao.querySxtTotalInvest(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * 统计稳盈宝投资总额
	 * 
	 * @return
	 * @throws SQLException
	 */

	public Map<String, String> queryWybTotalInvest() throws Exception {
		Connection conn = MySQL.getConnection();

		Map<String, String> map = new HashMap<String, String>();
		try {
			map = investDao.queryWybTotalInvest(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * 查询可用共富券
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryAvailableVoucherList(long userId,int voucherType) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryAvailableVoucherList(conn, userId, voucherType);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}
	
	/**
	 * 使用共富券
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Map<String, String> gofullVoucherReceive(long userId,
			long voucherId, long pBid, int productType, double investAmount)
			throws Exception {
		Connection conn = MySQL.getConnection();
		long ret = -1;
		DataSet ds = new DataSet();
		Map<String, String> map = new HashMap<String, String>();
		List<Object> outParameterValues = new ArrayList<Object>();
		try {
			Procedures.p_gofull_voucher_receive(conn, ds, outParameterValues,
					userId, voucherId, pBid, productType, new BigDecimal(investAmount), 0, "");
			ret = Convert.strToLong(outParameterValues.get(0) + "", -1);
			map.put("ret", ret + "");
			map.put("ret_desc", outParameterValues.get(1) + "");
			if (ret <= 0) {
				conn.rollback();
			} else {
			}
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}
	
	/**
	 * 查询用户个人余额宝产品总额度
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryYebInvestAmount(long userId) throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = financeDao.queryYebInvestAmount(conn, userId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return list;
	}

	public AwardService getAwardService() {
		return awardService;
	}

	public void setAwardService(AwardService awardService) {
		this.awardService = awardService;
	}

	public SelectedService getSelectedService() {
		return selectedService;
	}

	public void setSelectedService(SelectedService selectedService) {
		this.selectedService = selectedService;
	}

	public UserDao getUserDao() {
		return userDao;
	}

	public void setUserDao(UserDao userDao) {
		this.userDao = userDao;
	}

	public OperationLogDao getOperationLogDao() {
		return operationLogDao;
	}

	public void setOperationLogDao(OperationLogDao operationLogDao) {
		this.operationLogDao = operationLogDao;
	}

	public RepayMentDao getRepayMentDao() {
		return repayMentDao;
	}

	public void setRepayMentDao(RepayMentDao repayMentDao) {
		this.repayMentDao = repayMentDao;
	}

	public InvestDao getInvestDao() {
		return investDao;
	}

	public void setInvestDao(InvestDao investDao) {
		this.investDao = investDao;
	}

	public BorrowDao getBorrowDao() {
		return borrowDao;
	}

	public void setBorrowDao(BorrowDao borrowDao) {
		this.borrowDao = borrowDao;
	}

}
